{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Easy\n",
    "\n",
    "The \"Neeps\" database includes details of all teaching events in the School of Computing at Napier University in Semester 1 of the 2000/2001 academic year.\n",
    "\n",
    "Graduated questions\n",
    "\n",
    "The timetable database contains the following tables:\n",
    "\n",
    "- ut_staff\n",
    "\n",
    "id | name\n",
    "---|-----\n",
    "co.ACg | Cumming, Andrew\n",
    "co.ACr | Crerar, Dr Alison\n",
    "co.AFA | Armitage, Dr Alistair\n",
    " | ... Result truncated.\n",
    "\n",
    "The staff table simply gives the unique identifier for each member of staff together with their full name.\n",
    "\n",
    "- ut_student\n",
    "\n",
    "id | name | sze | parent\n",
    "---|------|----:|----------\n",
    "co1.BAe | BAe | 15 | \n",
    "co1.CO | Computing 1st Year | 150 | \n",
    "co1.CO.a | Computing 1st Year a | 20 | co1.CO\n",
    "co1.CO.c | Computing 1st Year c | 20 | co1.CO\n",
    "co1.CO.d | Computing 1st Year d | 20 | co1.CO\n",
    " | | | ... Result truncated.\n",
    "\n",
    "Each entry in the student table represents a group of students who follow the same timetable. The parent field is used to maintain a hierachy of groups. A student in group co1.CO.a is also in group co1.CO\n",
    "\n",
    "- ut_room\n",
    "\n",
    "id | name | capacity | parent\n",
    "---|------|---------:|---------\n",
    "co.117+118 |  | 32 | \n",
    "co.117 |  | 16 | co.117+118\n",
    "co.118 |  | 16 | co.117+118\n",
    "co.B7 |  | 40 | \n",
    " | | | ... result truncated\n",
    "\n",
    "All classrooms are listed in the room table. The parent relation is used to maintain groupings. The row co.117+118 represents a pair of rooms - sometimes these rooms are used for a single event.\n",
    "\n",
    "- ut_event\n",
    "\n",
    "id | modle | kind | dow | tod | duration | room\n",
    "---|-------|------|-----|-----|---------:|----------\n",
    "co12004.L01 | co12004 | L | Wednesday | 11:00 | 1 | cr.SMH\n",
    "co12004.L02 | co12004 | L | Monday | 17:00 | 1 | cr.B13\n",
    "co12004.T01 | co12004 | T | Monday | 11:00 | 2 | co.G78+G82\n",
    "co12004.T02 | co12004 | T | Tuesday | 15:00 | 2 | co.B7\n",
    "co12004.T03 | co12004 | T | Tuesday | 13:00 | 2 | co.G78+G82\n",
    " | | | | | | ... Result truncated.\n",
    "\n",
    "Every event is associated with a single modle (deliberately mis-spelled version of module - which is a reserved word in one of engines). An event occurs on a particular day of the week (dow) at a particular time of day (tod). Every event has a duration specified as a whole number of hours.\n",
    "\n",
    "- ut_attends\n",
    "\n",
    "student | event\n",
    "--------|------\n",
    "co1.BAe | co12004.L01\n",
    "co1.CO | co12004.L01\n",
    "co1.IS | co12004.L01\n",
    "co1.SE.pt | co12004.L02\n",
    "co1.CO.a | co12004.T01\n",
    " | ... Result truncated.\n",
    "\n",
    "The attends table links event to student. It realises the many to many relation.\n",
    "\n",
    "- ut_teaches\n",
    "\n",
    "staff | event\n",
    "------|----------\n",
    "co.AMn | co12004.L01\n",
    "co.RK | co12004.L01\n",
    "co.SRM | co12004.L01\n",
    "co.RK | co12004.L02\n",
    "co.SRM | co12004.L02\n",
    " | ... Result truncated.\n",
    "\n",
    "The teaches table links event to staff.\n",
    "\n",
    "- ut_occurs\n",
    "\n",
    "event | week\n",
    "------|-------:\n",
    "co12004.L01 | 01\n",
    "co12004.L01 | 02\n",
    "co12004.L01 | 03\n",
    "co12004.L01 | 04\n",
    "co12004.L01 | 05\n",
    " | ... Result truncated.\n",
    "\n",
    "Most events occur on weeks 01 to 13. This table records exactly which weeks are part of the plan for each event.\n",
    "\n",
    "- ut_modle\n",
    "\n",
    "id | name\n",
    "---|---------\n",
    "co12001 | Rapid Application Development\n",
    "co12002 | Software Development 1A\n",
    "co12003 | Professional Skills\n",
    "co12004 | Applications Workshop\n",
    " | ... Result truncated.\n",
    "\n",
    "By the 21st century, the Gradgrindians had completed their assimilation of Education. The process of \"modularisation\" was just one of their crimes against learning.\n",
    "\n",
    "- ut_week\n",
    "\n",
    "id | wkstart\n",
    "--:|-------:\n",
    "01 | 02/10/00\n",
    "02 | 09/10/00\n",
    "03 | 16/10/00\n",
    " | ...Result truncated\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "\n",
    "sc = (SparkSession.builder.appName('app15-1') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ut_staff = sc.read.table('sqlzoo.ut_staff')\n",
    "ut_student = sc.read.table('sqlzoo.ut_student')\n",
    "ut_event = sc.read.table('sqlzoo.ut_event')\n",
    "ut_room = sc.read.table('sqlzoo.ut_room')\n",
    "ut_attends = sc.read.table('sqlzoo.ut_attends')\n",
    "ut_teaches = sc.read.table('sqlzoo.ut_teaches')\n",
    "ut_occurs = sc.read.table('sqlzoo.ut_occurs')\n",
    "ut_modle = sc.read.table('sqlzoo.ut_modle')\n",
    "ut_week = sc.read.table('sqlzoo.ut_week')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the room id in which the event co42010.L01 takes place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cr.132</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     room\n",
       "0  cr.132"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ut_event.filter(ut_event['id']=='co42010.L01')\n",
    " .select('room')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**For each event in module co72010 show the day, the time and the place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dow</th>\n",
       "      <th>tod</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>14:00</td>\n",
       "      <td>cr.SMH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>09:00</td>\n",
       "      <td>cr.B8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>09:00</td>\n",
       "      <td>co.B7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>11:00</td>\n",
       "      <td>co.G75+G76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>16:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>10:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>13:00</td>\n",
       "      <td>co.117+118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         dow    tod          room\n",
       "0  Wednesday  14:00        cr.SMH\n",
       "1    Tuesday  09:00         cr.B8\n",
       "2  Wednesday  09:00         co.B7\n",
       "3    Tuesday  12:00  co.LB42+LB46\n",
       "4    Tuesday  11:00    co.G75+G76\n",
       "5  Wednesday  16:00  co.LB42+LB46\n",
       "6   Thursday  10:00  co.LB42+LB46\n",
       "7  Wednesday  13:00    co.117+118"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ut_event.filter(ut_event['modle']=='co72010')\n",
    " .select('dow', 'tod', 'room')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of the staff who teach on module co72010.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chisholm, Ken</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Cumming, Andrew</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name\n",
       "0    Chisholm, Ken\n",
       "1  Cumming, Andrew"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ut_event.filter(ut_event['modle']=='co72010')\n",
    " .join(ut_teaches, on=(ut_event['id']==ut_teaches['event']))\n",
    " .join(ut_staff, on=(ut_teaches['staff']==ut_staff['id']))\n",
    " .select('name')\n",
    " .distinct()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**Give a list of the staff and module number associated with events using room cr.132 on Wednesday, include the time each event starts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>modle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Varey, Alison</td>\n",
       "      <td>co32021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Murray, Jim</td>\n",
       "      <td>co22009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name    modle\n",
       "0  Varey, Alison  co32021\n",
       "1    Murray, Jim  co22009"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ut_event.filter((ut_event['room']=='cr.132') & \n",
    "                 (ut_event['dow']=='Wednesday'))\n",
    " .join(ut_teaches, on=(ut_event['id']==ut_teaches['event']))\n",
    " .join(ut_staff, on=(ut_teaches['staff']==ut_staff['id']))\n",
    " .select('name', 'modle')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Give a list of the student groups which take modules with the word 'Database' in the name.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>student_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BSc4 Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BSc4 Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BEng4 Network and Distributing Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BEng4 Network and Distributing Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PgD Information Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>PgD Information Systems a (HCI)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>PgD Information Systems b (DS)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>PgD Information Systems d (BT)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PgD Information Systems e (OOP)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PgD Information System pt. Tues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PgD Information System pt. Tues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>PgD IT and e-Commerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PgD IT and e-Commerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>PgD IT and e-Commerce eve sem 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>PgD Software Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>PgD Software Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>PgD Software Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>PgD Software Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PgD Software Technology pt. Tues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>PgD Software Technology pt. Tues</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             student_group\n",
       "0                           BSc4 Computing\n",
       "1                           BSc4 Computing\n",
       "2   BEng4 Network and Distributing Systems\n",
       "3   BEng4 Network and Distributing Systems\n",
       "4                  PgD Information Systems\n",
       "5          PgD Information Systems a (HCI)\n",
       "6           PgD Information Systems b (DS)\n",
       "7           PgD Information Systems d (BT)\n",
       "8          PgD Information Systems e (OOP)\n",
       "9          PgD Information System pt. Tues\n",
       "10         PgD Information System pt. Tues\n",
       "11                   PgD IT and e-Commerce\n",
       "12                   PgD IT and e-Commerce\n",
       "13                                    None\n",
       "14                                    None\n",
       "15         PgD IT and e-Commerce eve sem 3\n",
       "16                PgD Software Engineering\n",
       "17                PgD Software Engineering\n",
       "18                 PgD Software Technology\n",
       "19                 PgD Software Technology\n",
       "20        PgD Software Technology pt. Tues\n",
       "21        PgD Software Technology pt. Tues"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ut_student\n",
    " .withColumnRenamed('name', 'student_group')\n",
    " .join(ut_attends, on=(ut_student['id']==ut_attends['student']))\n",
    " .join(ut_event, on=(ut_attends['event']==ut_event['id']))\n",
    " .join(ut_modle.filter(lower(col('name')).like('%database%')), \n",
    "       on=(ut_event['modle']==ut_modle['id']))\n",
    " .select('student_group')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
